package com.demo.las.repository;

import com.demo.las.entity.StudyAchievement;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

@Repository
public interface StudyAchievementRepository extends JpaRepository<StudyAchievement, Long> {
    
    List<StudyAchievement> findByUserIdOrderByAchievementDateDesc(Long userId);
    
    List<StudyAchievement> findByUserIdAndAchievementTypeOrderByAchievementDateDesc(Long userId, StudyAchievement.AchievementType type);
    
    List<StudyAchievement> findByUserIdAndSubject(Long userId, String subject);
    
    List<StudyAchievement> findByUserIdAndAchievementDateBetween(Long userId, LocalDate startDate, LocalDate endDate);
    
    @Query("SELECT SUM(sa.achievementValue) FROM StudyAchievement sa WHERE sa.userId = :userId")
    BigDecimal getTotalAchievementValueByUser(@Param("userId") Long userId);
    
    @Query("SELECT SUM(sa.achievementValue) FROM StudyAchievement sa WHERE sa.userId = :userId AND sa.achievementDate BETWEEN :startDate AND :endDate")
    BigDecimal getTotalAchievementValueByUserAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT sa.achievementType, SUM(sa.achievementValue) as totalValue, COUNT(*) as count FROM StudyAchievement sa WHERE sa.userId = :userId GROUP BY sa.achievementType ORDER BY totalValue DESC")
    List<Object[]> getAchievementValueByType(@Param("userId") Long userId);
    
    @Query("SELECT sa.subject, SUM(sa.achievementValue) as totalValue FROM StudyAchievement sa WHERE sa.userId = :userId GROUP BY sa.subject ORDER BY totalValue DESC")
    List<Object[]> getAchievementValueBySubject(@Param("userId") Long userId);
    
    @Query("SELECT sa FROM StudyAchievement sa WHERE sa.userId = :userId AND sa.achievementDate >= :startDate ORDER BY sa.achievementDate DESC")
    List<StudyAchievement> findRecentAchievements(@Param("userId") Long userId, @Param("startDate") LocalDate startDate);
    
    @Query("SELECT COUNT(*) FROM StudyAchievement sa WHERE sa.userId = :userId AND sa.achievementType = :type")
    Long countAchievementsByType(@Param("userId") Long userId, @Param("type") StudyAchievement.AchievementType type);
    
    @Query("SELECT sa.achievementDate, SUM(sa.achievementValue) as dailyValue FROM StudyAchievement sa WHERE sa.userId = :userId AND sa.achievementDate BETWEEN :startDate AND :endDate GROUP BY sa.achievementDate ORDER BY sa.achievementDate")
    List<Object[]> getAchievementValueTrend(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
}




